package com.xunji.standard.service;

import com.xunji.standard.common.Constant;
import com.xunji.standard.dto.TXjNdFlBillInformationVo;
import com.xunji.standard.dto.TXjNdFlMessageCallVo;
import com.xunji.standard.dto.TXjNdFlVoiceCallVo;
import com.xunji.standard.entity.*;
import com.xunji.standard.util.ExcelUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author: xuqiang_imyours
 * @Date: 2025/7/9 17:55
 * @Description:
 **/
@Service
@Slf4j
public class ExcelProcessService {


    // 生成账单信息表的Excel文件
    public void processBillingInformationExcel(String savePath, String sheetName, List<TXjNdFlBillInformation> billingInformations) {
        log.info("开始处理账单信息Excel文件的导出，savePath:{},sheetName:{}", savePath, sheetName);
        try {
            if (billingInformations != null && !billingInformations.isEmpty()) {
                ArrayList<TXjNdFlBillInformationVo> vos = new ArrayList<>();
                for (TXjNdFlBillInformation billingInformation : billingInformations) {
                    TXjNdFlBillInformationVo vo = TXjNdFlBillInformationVo.getInstance(billingInformation);
                    vos.add(vo);
                }
                ExcelUtils.exportToExcel(vos, Constant.BILLING_INFORMATION_EXCEL_HEADER, sheetName, savePath);
            }
        } catch (Exception e) {
            log.error("生成账单信息表的Excel文件失败：{}", e.getMessage());
        }

    }

    // 生成短信通话信息表的Excel文件
    public void processSmsCallExcel(String savePath, String sheetName, List<TXjNdFlMessageCall> calls) {
        try {
            if (calls != null && !calls.isEmpty()) {
                ArrayList<TXjNdFlMessageCallVo> vos = new ArrayList<>();
                // 将数据转换成NdSmsCallExcelVo
                for (TXjNdFlMessageCall call : calls) {
                    TXjNdFlMessageCallVo vo = TXjNdFlMessageCallVo.getInstance(call);
                    vos.add(vo);
                }
                ExcelUtils.exportToExcel(vos, Constant.SMS_CALL_EXCEL_HEADER, sheetName, savePath);
            }
        } catch (Exception e) {
            log.error("生成短信通话信息表的Excel文件失败：{}", e.getMessage());
        }
    }

    // 生成语音通话信息表的Excel文件
    public void processVoiceCallExcel(String savePath, String sheetName, List<TXjNdFlVoiceCall> calls) {
        try {
            if (calls != null && !calls.isEmpty()) {
                List<TXjNdFlVoiceCallVo> vos = new ArrayList<>();
                for (TXjNdFlVoiceCall call : calls) {
                    TXjNdFlVoiceCallVo vo = TXjNdFlVoiceCallVo.getInstance(call);
                    vos.add(vo);
                }
                ExcelUtils.exportToExcel(vos, Constant.VOICE_CALL_EXCEL_HEADER, sheetName, savePath);
            }
        } catch (Exception e) {
            log.error("生成语音通话信息表的Excel文件失败：{}", e.getMessage());
        }
    }

    /**
     * 生成包含个人信息、联系方式、车辆信息、不动产信息和账户信息的Excel文件。
     *
     * @param savePath    保存Excel文件的路径，包括文件名和扩展名（例如："C:/output/person_info.xlsx"）。
     * @param sheetName   工作表的名称，用于标识Excel中的工作表。
     * @param person      包含个人基本信息的对象，类型为NdPersonInformation。
     * @param vehicles    包含车辆信息的列表，每个元素为NdMotorVehicle对象。
     * @param realEstates 包含不动产信息的列表，每个元素为NdRealEstate对象。
     *                    <p>
     *                    该函数会根据提供的数据生成一个格式化的Excel文件，并将其保存到指定路径。
     */
    public void processPersonInformationExcel(String savePath, String sheetName, TXjNdFlPersonInformation person, List<TXjNdFlCarInformation> vehicles, List<TXjNdFlHouseInformation> realEstates, List<TXjNdFlBankInformation> bankAccounts, List<TXjNdFlAlipayInformation> alipays, List<TXjNdFlWechatInformation> wechats) {

        log.info("开始处理人员信息Excel文件");
        // 创建工作簿和工作表
        Workbook workbook = new XSSFWorkbook(); // .xlsx格式
        Sheet sheet = workbook.createSheet(sheetName);

        // 创建样式（居中）
        CellStyle centerStyle = workbook.createCellStyle();
        centerStyle.setAlignment(HorizontalAlignment.CENTER); // 左对齐
        centerStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中

        // 添加背景色
        centerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        centerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 创建加粗字体
        Font centerStyleBoldFont = workbook.createFont();
        centerStyleBoldFont.setBold(true);
        centerStyleBoldFont.setFontHeightInPoints((short) 14);  // 设置字号为14
        // 设置黑体
        centerStyleBoldFont.setFontName("黑体");
        centerStyle.setFont(centerStyleBoldFont);


        // 在创建 centerStyle 后添加边框样式
        CellStyle borderStyle = workbook.createCellStyle();
        borderStyle.setBorderTop(BorderStyle.THIN);
        borderStyle.setBorderBottom(BorderStyle.THIN);
        borderStyle.setBorderLeft(BorderStyle.THIN);
        borderStyle.setBorderRight(BorderStyle.THIN);
        borderStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中

        CellStyle centerBorderStyle = workbook.createCellStyle();
        centerBorderStyle.cloneStyleFrom(centerStyle); // 继承居中属性
        centerBorderStyle.setBorderTop(BorderStyle.THIN);
        centerBorderStyle.setBorderBottom(BorderStyle.THIN);
        centerBorderStyle.setBorderLeft(BorderStyle.THIN);
        centerBorderStyle.setBorderRight(BorderStyle.THIN);


        // 创建标题样式
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.LEFT); // 左对齐
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中

        titleStyle.cloneStyleFrom(centerStyle); // 继承居中属性
        titleStyle.setBorderTop(BorderStyle.THIN);
        titleStyle.setBorderBottom(BorderStyle.THIN);
        titleStyle.setBorderLeft(BorderStyle.THIN);
        titleStyle.setBorderRight(BorderStyle.THIN);

        // 创建加粗字体
        Font titleFont = workbook.createFont();
        titleFont.setBold(true);
        titleFont.setFontHeightInPoints((short) 12);  // 设置字号为12
        // 设置黑体
        titleFont.setFontName("黑体");
        titleStyle.setFont(titleFont);


        // 设置列宽（单位：1/256个字符宽度）
        int columnWidth = 5;  // 列宽为5列
        for (int i = 0; i <= columnWidth; i++) {
            sheet.setColumnWidth(i, 30 * 256);
        }
        int currentRow = 0; // 当前行，0为起点

        Row row0 = sheet.createRow(currentRow);
        row0.setHeightInPoints(60); // 30点（约2厘米）
        // 填充第一行
        Cell cell0 = row0.createCell(0);
        String xm = person.getXm();
        cell0.setCellValue(xm == null ? "基本信息档案表" : xm + "基本信息档案表");
        cell0.setCellStyle(centerStyle);
        // 合并单元格：合并第0行A1到D1
        sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, 0, columnWidth));
        currentRow++;
        Row row1 = sheet.createRow(currentRow);
        Cell cell10 = row1.createCell(0);
        cell10.setCellValue("姓名：");
        cell10.setCellStyle(titleStyle);

        row1.createCell(1).setCellValue(xm == null ? "" : xm);
        Cell cell12 = row1.createCell(2);
        cell12.setCellValue("性别：");
        cell12.setCellStyle(titleStyle);

        if (person.getXb() == null) {
            Cell cell13 = row1.createCell(3);
            cell13.setCellValue("");
            cell12.setCellStyle(titleStyle);
        } else {
            Cell cell13 = row1.createCell(3);
            cell13.setCellValue("1".equals(person.getXb()) ? "男" : "女");
            cell12.setCellStyle(titleStyle);
        }
        Cell cell14 = row1.createCell(4);
        cell14.setCellValue("身份证号：");
        cell14.setCellStyle(titleStyle);

        String sfzh = person.getSfzh();
        row1.createCell(5).setCellValue(sfzh == null ? "" : sfzh);
        currentRow++;
        Row row2 = sheet.createRow(currentRow);
        Cell cell20 = row2.createCell(0);
        cell20.setCellValue("出生日期：");
        cell20.setCellStyle(titleStyle);

        LocalDateTime csrq = person.getCsrq();
        // 转化成字符串
        String csrqStr = csrq == null ? "" : csrq.format(DateTimeFormatter.ofPattern("yyyy年MM月dd日"));
        row2.createCell(1).setCellValue(csrqStr);
        Cell cell22 = row2.createCell(2);
        cell22.setCellValue("民族：");
        cell22.setCellStyle(titleStyle);

        String mz = person.getMz();
        row2.createCell(3).setCellValue(mz == null ? "" : mz);
        Cell cell24 = row2.createCell(4);
        cell24.setCellValue("文化程度：");
        cell24.setCellStyle(titleStyle);

        String whcd = person.getWhcd();
        row2.createCell(5).setCellValue(whcd == null ? "" : whcd);
        currentRow++;

        // 添加一行空数据
        addBlankRow(sheet, currentRow, columnWidth, centerStyle);
        currentRow++;

        /*Row row3 = sheet.createRow(currentRow); // 联系方式
        row3.setHeightInPoints(60);
        Cell cell3 = row3.createCell(0);
        cell3.setCellValue("联系方式");
        cell3.setCellStyle(centerStyle);
        sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, 0, columnWidth)); // 合并单元格
        currentRow++;
        Row row4 = sheet.createRow(currentRow);
        Cell cell40 = row4.createCell(0);
        cell40.setCellValue("登记时间");
        cell40.setCellStyle(titleStyle);

        Cell cell41 = row4.createCell(1);
        cell41.setCellValue("号码");
        cell41.setCellStyle(titleStyle);

        Cell cell42 = row4.createCell(2);
        cell42.setCellValue("类型");
        cell42.setCellStyle(titleStyle);

        Cell cell43 = row4.createCell(3);
        cell43.setCellValue("状态");
        cell43.setCellStyle(titleStyle);
        Cell cell44 = row4.createCell(4);
        cell44.setCellValue("运营商");
        cell44.setCellStyle(titleStyle);
        currentRow++;

        // 联系方式数据处理
        if (contacts != null && !contacts.isEmpty()) {
            for (NdContactInformation contact : contacts) {
                Row row = sheet.createRow(currentRow);
                row.createCell(0).setCellValue(contact.getRwsj() == null ? "" : contact.getRwsj());
                row.createCell(1).setCellValue(contact.getLxhm() == null ? "" : contact.getLxhm());
                row.createCell(2).setCellValue(contact.getHmlx() == null ? "" : contact.getHmlx());
                row.createCell(3).setCellValue(contact.getHmzt() == null ? "" : contact.getHmzt());
                row.createCell(4).setCellValue(contact.getYys() == null ? "" : contact.getYys());
                currentRow++;
            }
        }

        // 添加一行空数据
        addBlankRow(sheet, currentRow, columnWidth, centerStyle);
        currentRow++;*/

        Row row6 = sheet.createRow(currentRow); // 车辆信息
        row6.setHeightInPoints(60);
        Cell cell6 = row6.createCell(0);
        cell6.setCellValue("车辆信息");
        cell6.setCellStyle(centerStyle);
        sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, 0, columnWidth));
        currentRow++;
        Row row7 = sheet.createRow(currentRow);
        Cell cell70 = row7.createCell(0);
        cell70.setCellValue("登记时间");
        cell70.setCellStyle(titleStyle);

        Cell cell71 = row7.createCell(1);
        cell71.setCellValue("车辆品牌");
        cell71.setCellStyle(titleStyle);

        Cell cell72 = row7.createCell(2);
        cell72.setCellValue("车辆类型");
        cell72.setCellStyle(titleStyle);

        Cell cell73 = row7.createCell(3);
        cell73.setCellValue("车辆颜色");
        cell73.setCellStyle(titleStyle);

        Cell cell74 = row7.createCell(4);
        cell74.setCellValue("车辆号码");
        cell74.setCellStyle(titleStyle);

        Cell cell84 = row7.createCell(5);
        cell84.setCellValue("机动车所有人");
        cell84.setCellStyle(titleStyle);

        currentRow++;

        // 车辆信息处理
        if (vehicles != null && !vehicles.isEmpty()) {
            for (TXjNdFlCarInformation vehicle : vehicles) {
                Row row = sheet.createRow(currentRow);
                LocalDateTime ccdjrq = vehicle.getCcdjrq();
                String ccdjrqStr = ccdjrq == null ? "" : ccdjrq.format(DateTimeFormatter.ofPattern("yyyy年MM月dd日"));
                row.createCell(0).setCellValue(ccdjrqStr);
                row.createCell(1).setCellValue(vehicle.getZwpp() == null ? "" : vehicle.getZwpp());
                row.createCell(2).setCellValue(vehicle.getHpzl() == null ? "" : vehicle.getHpzl());
                row.createCell(3).setCellValue(vehicle.getCsys() == null ? "" : vehicle.getCsys());
                row.createCell(4).setCellValue(vehicle.getHphm() == null ? "" : vehicle.getHphm());
                row.createCell(5).setCellValue(vehicle.getJdcsyr() == null ? "" : vehicle.getJdcsyr());
                currentRow++;
            }
        }

        // 添加一行空数据
        addBlankRow(sheet, currentRow, columnWidth, centerStyle);
        currentRow++;

        Row row9 = sheet.createRow(currentRow); // 不动产信息
        row9.setHeightInPoints(60);
        Cell cell9 = row9.createCell(0);
        cell9.setCellValue("不动产信息");
        cell9.setCellStyle(centerStyle);
        sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, 0, columnWidth));
        currentRow++;
        Row row10 = sheet.createRow(currentRow);
        Cell cell110 = row10.createCell(0);
        cell110.setCellValue("登记时间");
        cell110.setCellStyle(titleStyle);

        Cell cell111 = row10.createCell(1);
        cell111.setCellValue("城市");
        cell111.setCellStyle(titleStyle);

        Cell cell112 = row10.createCell(2);
        cell112.setCellValue("位置");
        cell112.setCellStyle(titleStyle);

        Cell cell113 = row10.createCell(3);
        cell113.setCellValue("面积");
        cell113.setCellStyle(titleStyle);

        Cell cell114 = row10.createCell(4);
        cell114.setCellValue("共有人");
        cell114.setCellStyle(titleStyle);

        Cell cell115 = row10.createCell(5);
        cell115.setCellValue("");
        cell115.setCellStyle(titleStyle);

        currentRow++;

        // 处理不动产信息
        if (realEstates != null && !realEstates.isEmpty()) {
            for (TXjNdFlHouseInformation realEstate : realEstates) {
                Row row = sheet.createRow(currentRow);
                LocalDateTime djsj = realEstate.getDjsj();
                String djsjStr = djsj == null ? "" : djsj.format(DateTimeFormatter.ofPattern("yyyy年MM月dd日"));
                row.createCell(0).setCellValue(djsjStr);
                row.createCell(1).setCellValue(realEstate.getFwzl() == null ? "" : realEstate.getFwzl());
                row.createCell(2).setCellValue(realEstate.getBdcdyh() == null ? "" : realEstate.getBdcdyh());
                row.createCell(3).setCellValue(realEstate.getJzmj() == null ? "" : realEstate.getJzmj());
                row.createCell(4).setCellValue(realEstate.getGyr() == null ? "" : realEstate.getGyr());
                currentRow++;
            }
        }

        // 添加一行空数据
        addBlankRow(sheet, currentRow, columnWidth, centerStyle);
        currentRow++;

        Row row12 = sheet.createRow(currentRow); // 账户信息
        row12.setHeightInPoints(60);
        Cell cells12 = row12.createCell(0);
        cells12.setCellValue("账户信息");
        cells12.setCellStyle(centerStyle);
        sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, 0, columnWidth));
        currentRow++;

        Row row13 = sheet.createRow(currentRow); // 账户信息
        Cell cell130 = row13.createCell(0);
        cell130.setCellValue("开户日期");
        cell130.setCellStyle(titleStyle);

        Cell cell131 = row13.createCell(1);
        cell131.setCellValue("类型");
        cell131.setCellStyle(titleStyle);

        Cell cell132 = row13.createCell(2);
        cell132.setCellValue("开户网点");
        cell132.setCellStyle(titleStyle);

        Cell cell133 = row13.createCell(3);
        cell133.setCellValue("卡号");
        cell133.setCellStyle(titleStyle);

        Cell cell134 = row13.createCell(4);
        cell134.setCellValue("状态");
        cell134.setCellStyle(titleStyle);

        Cell cell135 = row13.createCell(5);
        cell135.setCellValue("");
        cell135.setCellStyle(titleStyle);

        currentRow++;

        // 处理账户信息
        if (bankAccounts != null && !bankAccounts.isEmpty()) {
            for (TXjNdFlBankInformation bankAccount : bankAccounts) {
                Row row = sheet.createRow(currentRow);
                LocalDateTime khrq = bankAccount.getKhrq();
                String khrqStr = khrq == null ? "" : khrq.format(DateTimeFormatter.ofPattern("yyyy年MM月dd日"));
                row.createCell(0).setCellValue(khrqStr);
                row.createCell(1).setCellValue("卡");
                row.createCell(2).setCellValue(bankAccount.getKhwd() == null ? "" : bankAccount.getKhwd());
                row.createCell(3).setCellValue(bankAccount.getKh() == null ? "" : bankAccount.getKh());
                row.createCell(4).setCellValue(bankAccount.getZhzt() == null ? "" : bankAccount.getZhzt());
                row.createCell(5).setCellValue("");
                currentRow++;
            }
        }
        // 处理微信账户
        if (wechats != null && !wechats.isEmpty()) {
            for (TXjNdFlWechatInformation wechat : wechats) {
                Row row = sheet.createRow(currentRow);
                LocalDateTime zcsj = wechat.getZcsj();
                String zcsjStr = zcsj == null ? "" : zcsj.format(DateTimeFormatter.ofPattern("yyyy年MM月dd日"));
                row.createCell(0).setCellValue(zcsjStr);
                row.createCell(1).setCellValue("微信");
                row.createCell(2).setCellValue("微信");
                row.createCell(3).setCellValue(wechat.getXm() == null ? "" : wechat.getXm());
                row.createCell(4).setCellValue(wechat.getZhzt() == null ? "" : wechat.getZhzt());
                row.createCell(5).setCellValue("");
                currentRow++;
            }
        }

        // 处理支付宝账户信息
        if (alipays != null && !alipays.isEmpty()) {
            for (TXjNdFlAlipayInformation alipay : alipays) {
                Row row = sheet.createRow(currentRow);
                LocalDateTime zcsj = alipay.getZcsj();
                String zcsjStr = zcsj == null ? "" : zcsj.format(DateTimeFormatter.ofPattern("yyyy年MM月dd日"));
                row.createCell(0).setCellValue(zcsjStr);
                row.createCell(1).setCellValue("支付宝");
                row.createCell(2).setCellValue("支付宝");
                row.createCell(3).setCellValue(alipay.getZfbid() == null ? "" : alipay.getZfbid());
                row.createCell(4).setCellValue("正常");
                row.createCell(5).setCellValue("");
                currentRow++;
            }
        }

        // 添加所有框线

        // 在保存文件前添加边框遍历逻辑（放在 finally 前）
        for (int r = 0; r <= currentRow; r++) {
            Row row = sheet.getRow(r);
            if (row == null) continue;

            for (int c = 0; c <= columnWidth; c++) { // 遍历6列
                Cell cell = row.getCell(c);
                if (cell == null) {
                    cell = row.createCell(c); // 创建空单元格
                    cell.setCellStyle(borderStyle);
                } else {
                    // 标题行用 centerBorderStyle，其他用 borderStyle
                    if (cell.getCellStyle().equals(centerStyle)) {
                        cell.setCellStyle(centerBorderStyle);
                    } else if (cell.getCellStyle().equals(titleStyle)) {
                        cell.setCellStyle(titleStyle);
                    } else {
                        cell.setCellStyle(borderStyle);
                    }
                }
            }
        }

        // 保存文件
        try {
            // 确保目录存在
            File file = new File(savePath);
            File parentDir = file.getParentFile();
            if (parentDir != null && !parentDir.exists()) {
                parentDir.mkdirs(); // 创建所有不存在的父目录
            }
            FileOutputStream fos = new FileOutputStream(savePath);
            workbook.write(fos);
            log.info("Excel文件生成成功!生成地址：{}", savePath);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 在指定的工作表中添加一个空白行，并设置该行的高度和样式
     * 此方法用于在处理表格时，需要分隔不同数据区块，通过添加带有特定样式的空白行来提高表格的可读性
     *
     * @param sheet       工作表对象，表示要在其中添加空白行的表格
     * @param currentRow  要添加的空白行的行号
     * @param columnWidth 合并单元格的宽度，用于指定要合并的列数
     * @param centerStyle 应用于空白行的单元格样式，通常用于居中对齐等内容
     */
    private void addBlankRow(Sheet sheet, int currentRow, int columnWidth, CellStyle centerStyle) {
        // 创建指定行号的新行
        Row row3 = sheet.createRow(currentRow); // 联系方式
        // 设置当前行的高度
        row3.setHeightInPoints(30);
        // 在当前行的第一列创建一个新单元格
        Cell cell3 = row3.createCell(0);
        // 设置单元格内容为空字符串，用于占位
        cell3.setCellValue("");
        // 应用传入的单元格样式到当前单元格
        // cell3.setCellStyle(centerStyle);
        // 合并从第一列到指定列宽的单元格，以创建一个跨越多列的空白区域
        sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, 0, columnWidth)); // 合并单元格
    }


}
